//=============================================================================
// CoolStorage.NET - .NET Object Relational Mapping Library 
//
// Copyright (c) 2004-2007 Philippe Leybaert
//
// Permission is hereby granted, free of charge, to any person obtaining a copy 
// of this software and associated documentation files (the "Software"), to deal 
// in the Software without restriction, including without limitation the rights 
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 
// copies of the Software, and to permit persons to whom the Software is 
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in 
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
// FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
// IN THE SOFTWARE.
//=============================================================================

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Activa.CoolStorage
{
	public class CSDataProviderSqlServer : CSDataProvider
	{
		public CSDataProviderSqlServer(string connectionString) : base(connectionString)
		{
		}

		protected override IDbConnection CreateConnection()
		{
			SqlConnection conn = new SqlConnection(ConnectionString);

			conn.Open();

			return conn;
		}

		public CSDataProviderSqlServer(string serverName, string databaseName)
			: this("Initial Catalog=" + databaseName + ";Data Source=" + serverName + ";Integrated Security=true;")
		{
		}

		protected override CSDataProvider Clone()
		{
			return new CSDataProviderSqlServer(ConnectionString);
		}

		protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
		{
			SqlCommand sqlCommand = (SqlCommand) Connection.CreateCommand();

			sqlCommand.Transaction = (SqlTransaction) CurrentTransaction;

            if (sqlQuery.StartsWith("!"))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = sqlQuery.Substring(1);
            }
            else
            {
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlQuery;
            }



			if (parameters != null && !parameters.IsEmpty)
				foreach (CSParameter csParameter in parameters)
				{
					IDbDataParameter dataParameter = sqlCommand.CreateParameter();

					dataParameter.ParameterName = csParameter.Name;
					dataParameter.Direction = ParameterDirection.Input;
                    dataParameter.Value = ConvertParameter(csParameter.Value);

					sqlCommand.Parameters.Add(dataParameter);
				}

			return sqlCommand;
		}


	    protected override string BuildSelectSQL(string tableName, string tableAlias, string[] columnList, string[] columnAliasList, string[] joinList, string whereClause, string orderBy, int maxRows, bool quoteColumns)
	    {
			StringBuilder sql = new StringBuilder(100);

			sql.Append("SELECT");

            if (maxRows > 0)
                sql.Append(" TOP " + maxRows);

            if (quoteColumns)
                columnList = QuoteFieldList(columnList);

			string[] columnNames = new string[columnList.Length];

			for (int i = 0; i < columnList.Length; i++)
			{
				columnNames[i] = columnList[i];
				
				if (columnAliasList != null)
					columnNames[i] += " " + columnAliasList[i];
			}

			sql.Append(' ');
			sql.Append(String.Join(",", columnNames));
			sql.Append(" FROM ");
			sql.Append(QuoteTable(tableName));
			sql.Append(' ');
			sql.Append(tableAlias);

            if (joinList != null && joinList.Length > 0)
                foreach (string joinExpression in joinList)
                    sql.Append(" " + joinExpression);

            if (whereClause != null && whereClause.Length > 0)
                sql.Append(" WHERE " + whereClause);

            if (orderBy != null && orderBy.Length > 0)
                sql.Append(" ORDER BY " + orderBy);

            return sql.ToString();
	        
	    }

	    protected override string BuildInsertSQL(string tableName, string[] columnList, string[] valueList, string[] primaryKeys, string identityField)
        {
            string sql;

            if (((SqlConnection)Connection).ServerVersion.StartsWith("09.") && primaryKeys != null && primaryKeys.Length > 0)
            {
                List<string> outputFields = new List<string>();

                foreach (string s in primaryKeys)
                    outputFields.Add("inserted." + s);

                if (columnList.Length > 0)
                {
                    sql = String.Format("insert into {0} ({1}) output {3} values ({2})",
                                        QuoteTable(tableName),
                                        String.Join(",", QuoteFieldList(columnList)),
                                        String.Join(",", valueList),
                                        String.Join(",", QuoteFieldList(outputFields.ToArray()))
                                       );
                }
                else
                {
                    sql = String.Format("insert into {0} output {1} default values", QuoteTable(tableName),String.Join(",", QuoteFieldList(outputFields.ToArray())));
                }
            }
            else
            {
                if (columnList.Length > 0)
                {
                    sql = String.Format("insert into {0} ({1}) values ({2})",
                                        QuoteTable(tableName),
                                        String.Join(",", QuoteFieldList(columnList)),
                                        String.Join(",", valueList)
                                        );
                }
                else
                {
                    sql = String.Format("insert into {0} default values",QuoteTable(tableName));
                }

                if (primaryKeys != null && primaryKeys.Length > 0 && identityField != null)
                    sql += String.Format(";SELECT {0} from {1} where {2} = SCOPE_IDENTITY()", String.Join(",",QuoteFieldList(primaryKeys)),QuoteTable(tableName),identityField);
            }

            return sql;
        }

		protected override string QuoteField(string fieldName) 
		{
			int dotIdx = fieldName.IndexOf('.');

			if (dotIdx > 0)
				return fieldName.Substring(0, dotIdx + 1) + "[" + fieldName.Substring(dotIdx + 1) + "]";
			else
				return "[" + fieldName + "]";
		}

		protected override string QuoteTable(string tableName) { return "[" + tableName.Replace(".", "].[") + "]"; }

		protected override string NativeFunction(string functionName, ref string[] parameters)
        {
            switch (functionName.ToUpper())
            {
                default: return functionName.ToUpper();
            }
        }

		protected override bool SupportsNestedTransactions
		{
			get { return false; }
		}

	    protected override bool SupportsSequences
	    {
	        get { return false; }
	    }

	    protected override bool SupportsMultipleStatements
	    {
	        get { return true; }
	    }

	    protected override bool RequiresSeperateIdentityGet
	    {
	        get { return false; }
	    }
	}
}
